package com.pennphoto.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import com.pennphoto.client.ProfessorService;
import com.pennphoto.client.RegisterService;
import com.pennphoto.client.StudentService;
import com.pennphoto.shared.FieldVerifier;
import com.pennphoto.shared.PennPhotoConstants;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;


/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class Stud extends RemoteServiceServlet implements
		StudentService {
	
	

	public Integer greetServer2(String c_yr,String major,String advisor,String gpa) throws Exception {
		//String success="Registration unsuccessful";
		
		c_yr = escapeHtml(c_yr);
		major = escapeHtml(major);
		advisor = escapeHtml(advisor);
		gpa = escapeHtml(gpa);
		int cur_yr=Integer.parseInt(c_yr);
		int advisorid=Integer.parseInt(advisor);
		double GPA=Double.parseDouble(gpa);
		int maxuserid=-1;
		int valid=1;
		Class.forName("oracle.jdbc.OracleDriver").newInstance();
    	String password="";
	    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
	   try {
		
	     Statement stmt1 = conn.createStatement();
	     try {
	       ResultSet rset1 = stmt1.executeQuery("select max(userid) from "+PennPhotoConstants.schemaName+"users");
	       
	       try {
	         while (rset1.next()){
	           
	           maxuserid=rset1.getInt(1);
	            // Print col 1
	         }
	       } 
	       finally {
	          try { rset1.close(); } catch (Exception ignore) {}
	       }
	     } 
	     finally {
	       try { stmt1.close(); } catch (Exception ignore) {}
	     }
	     
	     
	 		
		     Statement stmt3 = conn.createStatement();
		     try {
		    	 
		       ResultSet rset3 = stmt3.executeQuery("insert into "+PennPhotoConstants.schemaName+"student values("+maxuserid+",'"+major+"',"+cur_yr+","+GPA+",'"+advisorid+"')");
		    	//success="Your Information has been saved"; 
		       
		     } 
		     finally {
		       try { stmt3.close();  } catch (Exception ignore) {}
		     }
		     
		     
	     
	   
	   } 
	   finally {
	     try { conn.close(); } catch (Exception ignore) {}
	   }
		

		return maxuserid;
	} 
	
	public HashMap<Integer,String> getAdvisors() throws Exception{
		HashMap<Integer,String> advisors = new HashMap<Integer,String>();
		Class.forName("oracle.jdbc.OracleDriver").newInstance();
    	String password="";
	    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
	   try {
		
	     Statement stmt2 = conn.createStatement();
	     try {
	       ResultSet rset2 = stmt2.executeQuery("select profid,firstname,lastname from "+PennPhotoConstants.schemaName+"users,"+PennPhotoConstants.schemaName+"professor where userid = profid");
	       
	       try {
	         while (rset2.next()){
	           
	        	 advisors.put(rset2.getInt(1),rset2.getString(2)+" "+rset2.getString(3));
	            // Print col 1
	         }
	       } 
	       finally {
	          try { rset2.close(); } catch (Exception ignore) {}
	       }
	     } 
	     finally {
	       try { stmt2.close(); } catch (Exception ignore) {}
	     }
	   }
	     finally {
		     try { conn.close(); } catch (Exception ignore) {}
		   }
	     
		return advisors;
	}
	/**
	 * Escape an html string. Escaping data received from the client helps to
	 * prevent cross-site script vulnerabilities.
	 * 
	 * @param html the html string to escape
	 * @return the escaped string
	 */
	private String escapeHtml(String str) {
		if (str == null) {
			return null;
		}
		return str.replaceAll("&", "&amp;").replaceAll("<", "&lt;")
				.replaceAll(">", "&gt;");
	}
}


